﻿using System;
using System.Collections.Generic;
using System.Text;
using WordService.Data.IDAL;
using System.Data.SqlClient;
using WordService.DBUtil;
using System.Data;
using WordService.Model; 

namespace WordService.Data.DAL
{
    public sealed class InstitutionDAL : IInstitutionDAL 
    {
        private const String INSTITUTION_INSERT = @"" 
            + "INSERT INTO BAS_INSTITUTION(NAME,SEQUENCE,ARTICLE_ID) VALUES (@Name,@Sequence,@ArticleID) SELECT @@IDENTITY AS ID;";
        private const String INSTITUTION_QUERY = @"SELECT * FROM BAS_INSTITUTION";
        public Int64 InsertInstitution(String name, Int32 sequence, Int64 articleID)
        {
            if (name.Contains(","))
            {
                name = name.Split(',')[0];
            }
            // 初始化参数
            SqlParameter[] institutionParms = new SqlParameter[]{
                new SqlParameter("@Name",name),
                new SqlParameter("@Sequence",sequence),
                new SqlParameter("@ArticleID",articleID)
            };
            // 初始化连接
            Int64 ID = 0L;
            using (SqlDataReader dr = DBUtility.ExecuteReader(
                DBUtility.ConnectionStr,
                CommandType.Text,
                INSTITUTION_INSERT,
                institutionParms))
            {
                while (dr.Read())
                {
                    ID = Convert.ToInt64(dr["ID"]);
                }
            }
            return ID;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="keywordName"></param>
        /// <param name="authorName"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public IList<Institution> QueryByCondition(string keywordName, string authorName, string year)
        {
            #region 动态生成SQL语句
            IList<String> sqlList = new List<String>();
            if (year != null && !year.Trim().Equals(String.Empty))
            {
                sqlList.Add("(ARTICLE_ID IN (SELECT ARTICLE_ID FROM BAS_ARTICLE WHERE PUBLISHDATE LIKE '%" + year + "%'))");
            }
           // if (frequency != 0)
            //{
                //sqlList.Add("(TOTALCOUNT > " + frequency + ")");
            //}
            if (authorName != null && !authorName.Trim().Equals(String.Empty))
            {
                sqlList.Add("(ARTICLE_ID IN (SELECT ARTICLE_ID FROM BAS_AUTHOR WHERE NAME LIKE '%" + authorName + "%'))");
            }
            if (keywordName != null && !keywordName.Trim().Equals(String.Empty))
            {
                sqlList.Add("(ARTICLE_ID IN (SELECT ARTICLE_ID FROM BAS_KEYWORD WHERE NAME LIKE '%" + keywordName + "%'))");
            }
            StringBuilder builder = new StringBuilder();
            builder.Append(INSTITUTION_QUERY);
            builder.Append(DBUtility.GenerateSQLCondition(sqlList));
            #endregion

            IList<Institution> institutionList = new List<Institution>();
            using (SqlDataReader dr = DBUtility.ExecuteReader(
                DBUtility.ConnectionStr,
                CommandType.Text,
                builder.ToString()))
            {
                while (dr.Read())
                {
                    Institution institution = new Institution();
                    institution.ID = Convert.ToInt64(dr["ID"]);
                    institution.Name = dr["NAME"].ToString();
                    institution.Sequence = Convert.ToInt32(dr["SEQUENCE"]);
                    Article article = new Article();
                    article.ID = Convert.ToInt64(dr["ARTICLE_ID"]);
                    institution.Article = article;
                    institutionList.Add(institution);
                }
            }
            return institutionList;
        }
    }
}
